package com.englisheveryday.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.englisheveryday.consants.SQLConstants;
import com.englisheveryday.response.CategoryResponse;
import com.englisheveryday.vo.CategoryVO;

/**
 * <pre>
 *    
 * </pre>
 * @author 马永华
 * @date 2012-9-16 上午10:59:54
 * @version 1.1
 */
@SuppressWarnings("unchecked")
public class CategoryDAO {

   private JdbcTemplate jdbcTemplate;
	
	public CategoryDAO(JdbcTemplate jdbcTemplate){
		this.jdbcTemplate = jdbcTemplate;
	}
	
	//同步
	public List<CategoryVO> sync(long lastAnchor,long nextAnchor){
		List<CategoryVO> dataList = new ArrayList<CategoryVO>();
		try{
		final Map<String,String> changeMap = new HashMap<String,String>();
		StringBuilder addSb = new StringBuilder();
		StringBuilder updateSb = new StringBuilder();
		
		//获得change列表
	     jdbcTemplate.query(SQLConstants.changelist_category, new Object[]{lastAnchor,nextAnchor}, new RowMapper(){
			@Override
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				String dataid = rs.getString("dataid");
				String type = rs.getString("type");
				if(dataid!=null&&!"".equals(dataid)&&!"null".equals(dataid)){
					changeMap.put(dataid, changeMap.get(dataid)+type);
				}
				return null;
			}
		});
	     
		//组织待更新数据
		for(String key : changeMap.keySet()){
			String typeStr = changeMap.get(key);
			typeStr = typeStr.replace("null", "");
			System.out.println(key+""+typeStr);
			if((typeStr.startsWith("A")&&typeStr.endsWith("U"))||"A".equals(typeStr)){
			//添加
				addSb.append("'"+key+"'").append(",");
			}else if((typeStr.startsWith("U")&&typeStr.endsWith("D"))||"D".equals(typeStr)){
		   //删除
				CategoryVO edv = new CategoryVO();
				edv.setId(key);
				edv.setType("D");
				dataList.add(edv);
			}else if((typeStr.startsWith("U")&&typeStr.endsWith("U"))||"U".equals(typeStr)){
			//修改	
				updateSb.append("'"+key+"'").append(",");
			}
			
		}
		//添加
		if(addSb.length()>0)
		dataList.addAll(query(addSb.deleteCharAt(addSb.length()-1).toString(),"A"));
		//修改
		if(updateSb.length()>0)
		dataList.addAll(query(updateSb.deleteCharAt(updateSb.length()-1).toString(),"U"));
		
		}catch(Exception e){
			e.printStackTrace();
		}
		return dataList;
	}
	
	//查询
	public List<CategoryVO> query(String params,final String type){
	    //拼接要查询的sql
		StringBuilder  querysql = new StringBuilder();
		querysql.append(SQLConstants.sync_category);
		querysql.append("(");
		querysql.append(params);
		querysql.append(")");
		return jdbcTemplate.query(querysql.toString(), new RowMapper(){
			@Override
			public CategoryVO mapRow(ResultSet rs, int rowNum) throws SQLException {
				CategoryVO vo = new CategoryVO();
				vo.setId(rs.getString("id"));
				vo.setName(rs.getString("name"));
				vo.setType(type);
				return vo;
			}
		});
	}
	
	//查询每日口语数据
	public List<CategoryVO> query_category(int page,final int count){
		return jdbcTemplate.query(SQLConstants.query_category,new Object[]{page,count}, new RowMapper(){
			@Override
			public CategoryVO mapRow(ResultSet rs, int rowNum) throws SQLException {
				CategoryVO vo = new CategoryVO();
				vo.setId(rs.getString("id"));
				vo.setName(rs.getString("name"));
				return vo;
			}
		});
	}
	
	//保存
	public int save(String id,String name){
		return jdbcTemplate.update(SQLConstants.save_category, new Object[]{
				id,
				name
		});
	}
	//删除
	public int delete(String id){
		return jdbcTemplate.update(SQLConstants.delete_category, new Object[]{id});
	}
	
	//根据ID查询
	public CategoryResponse get(String id){
		List<CategoryResponse> list =  jdbcTemplate.query(SQLConstants.get_category,new Object[]{id}, new RowMapper<CategoryResponse>(){
			@Override
			public CategoryResponse mapRow(ResultSet rs, int rowNum) throws SQLException {
				CategoryResponse vo = new CategoryResponse();
				vo.setName(rs.getString("name"));
				vo.setId(rs.getString("id"));
				return vo;
			}
		});
		if(list!=null&&list.size()>0){
			return list.get(0);
		}
		return null;
	}
	
	//更新
	public int update(String id,String name){
		return jdbcTemplate.update(SQLConstants.update_category, new Object[]{
				name,
				id
			 });
	}
}

